#!perl  -I../scripts/lib

use Spreadsheet::ParseExcel;
use Product;
use DBI;
use Run;
use Connect;
use Log;

sub cmdline_error
        {
        my ($message) = @_;
        warn "$message\n" if (defined $message);
        print "Error command\n";
        exit;
        }

my ($book_n,$db,$user,$pw)=@ARGV;
unless (($book_n) and ($db) and ($user) and ($pw) )
{
cmdline_error ("usage:/data/tools/bin/perl test.pl <location of client file> <database> <user> <pw>");
}
my $dbh = Connect::db_con("$db","$user","$pw");





my @format_error=();
my @format_error_1=();
my @format_error_2=();
my @format_error_3=();
my @error=();
my @error_1=();
my @error_2=();
my @report_key_diff=();
my @sp=("\\n","\:","\;","\,");
my $product=new Product;
($product)=$product->excel($book_n,"CLIENT");
my ($oWKS,$oWKC);
my $oBook =$product->{'CLIENT'}{'oBook'};
SHEET:for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{
        $oWkS = $oBook->{Worksheet}[$iSheet];
        my $sheet= $oWkS->{Name};
	if ($sheet=~/SUMMARY/i)
	{
		next SHEET;
	}
	if ($sheet=~/Country/i)
	{
		next SHEET;
	}
	COM:for(my $iR = $oWkS->{MinRow} ;defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;$iR++)
	{
                for(my $iC = $oWkS->{MinCol} ; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;  $iC++)
               	{
                        $oWkC = $oWkS->{Cells}[$iR][$iC];
			if ($oWkC)
			{
				if ($oWkC->Value =~ "CUSTOMERS")
				{
					$product->{$sheet}{STIC}=$iC;
					$product->{$sheet}{STIR}=$iR;
					last COM;
				}
			}
		}
	}
	if ((defined $product->{$sheet}{STIC}) and (defined $product->{$sheet}{STIR}))
	{
		my $low =$product->{$sheet}{STIR}+1; 
		my @count=();
		while ($oWkC = $oWkS->{Cells}[$low][$product->{$sheet}{STIC}])
		{
			if ($oWkC->Value)
			{
				my $sql_stmnt = "SELECT id,name FROM country  WHERE code=\"$sheet\"";
        		        my ($sth,$result) = Run::ex($sql_stmnt,$dbh);
        			my @country= $sth->fetchrow_array;
	       			Run::finish($sth);
				if (@country)
				{
					$product->{$sheet}{$low}{'COUNTRY_ID'}=$country[0];
					$product->{$sheet}{$low}{'COUNTRY_NAME'}=$country[1];
					my $country=$product->{$sheet}{$low}{'COUNTRY_NAME'};
					my $company=$oWkC->Value;
					$company=~ s/\n/ /g;
					$product->{$sheet}{$low}{'COMPANY_NAME'}=$company;
					$company=~ s/\"/\\"/g;
					$sql_stmnt = "SELECT id FROM company  WHERE name=\"$company\"";
			                ($sth,$result) = Run::ex($sql_stmnt,$dbh);
			                my @company= $sth->fetchrow_array;
		        	        Run::finish($sth);
					if (@company)
					{
						$product->{$sheet}{$low}{'COMPANY_ID'}=shift @company;
						push @count,$low;
					}
					else
					{
						my $sql_insert = "INSERT INTO company (name) VALUE (\"$company\")";
				                ($sth,$result) = Run::ex($sql_insert,$dbh);
		        		        Run::finish($sth);
						if ($result ne "1")
		                        	{
				                        push @error, "ERROR:error to insert $sql_insert";
							$low++;
				                        next;
						}
						else
						{
				        	        Run::finish($sth);
				                	$sql_stmnt = "SELECT id FROM company  WHERE name=\"$company\"";
	                				($sth,$result) = Run::ex($sql_stmnt,$dbh);
			        		        my @company= $sth->fetchrow_array;
			                		Run::finish($sth);
							if (@company)
							{
								$product->{$sheet}{$low}{'COMPANY_ID'}=shift @company;
								push @count,$low;
							}
							else
							{
		                                                push @error, "ERROR:error to insert $sql_insert";
								$sql_stmnt_del="DELETE FROM company WHERE id=$product->{$sheet}{$count[$i]}{'COMPANY_ID'}";
	        	        	                        my ($sth_del,$result_del) = Run::ex($sql_stmnt_del,$dbh);
        	        	        	                Run::finish($sth_del);
								$low++;
								next;
							}
						}
					}
				}
				else
				{
                                	push @error_1, "ERROR:cannot find the country name $sheet";
					next SHEET;
				}
			}
			$low++;
		}

		my $i = 0;
		my $c = scalar (@count);
		while ($i < $c)
		{
			my $line =$count[$i];
			CUST:while (($line < $count[$i+1]) or (! defined $count[$i+1]))
			{
				my $next = $product->{$sheet}{STIC}+1;
				FIELD:while ($oWkC = $oWkS->{Cells}[$line][$next])
	        		{
		                	if ($oWkC->Value)
               				{
						my $value=$oWkC->Value;
						$value =~ s/\s//g;
						$value =~ s/\n//g;
						if ($value =~ /shipto/i)
						{
							my $ship = $oWkS->{Cells}[$line][$next+1]->Value;
							if ($ship eq "")
							{
								push (@format_error,"ship to field is empty in sheet:$sheet,\tcompany:$product->{$sheet}{$count[$i]}{'COMPANY_NAME'}");
							}
							while (($ship=~ /^\s/) or ($ship=~ /\s$/))
                                                        {
	                                                        $ship=~ s/^\s//;
                                                                $ship=~ s/\s$//;
                                                        }

							if ($ship =~ /^[a-zA-Z]/)
							{
								$ship =~ s/^[a-zA-Z]//;
							}
							if ($ship =~ /\D/)
							{
								push (@format_error_1,"ship to field's format error in sheet:$sheet,\tcompany:$product->{$sheet}{$count[$i]}{'COMPANY_NAME'},\tship to:$ship");
								$product->{$sheet}{$line}{'SHIPTO'} = "";
								last FIELD;
							}
							$product->{$sheet}{$line}{'SHIPTO'} = $ship;
							last FIELD;
						}
			
						elsif ($value =~ /SIMLOCK/i)
						{	
							my $maillist;
							my $ship = $oWkS->{Cells}[$line][$next+1]->Value;
							if ($ship eq "")
							{
								$ship = $oWkS->{Cells}[$line][$next+2]->Value;
								if ($ship eq "")
								{
									$ship = $oWkS->{Cells}[$line-1][$next+1]->Value;
									if ($ship eq "")
									{
										$ship = $oWkS->{Cells}[$line-1][$next+2]->Value;
									}
								}
							}
							if ($ship)
							{
								foreach my $sp (@sp)							
								{	
									if ($ship=~ /$sp/)
									{
										$ship=~ s/$sp/ /g;
									}
								}
								my @mail=split (/\s/,$ship);  
								foreach my $mail (@mail)
								{
									while (($mail=~ /^\s/) or ($mail=~ /\s$/))
									{
										$mail=~ s/^\s//;
										$mail=~ s/\s$//;
									}
									if ($mail=~ /(.*)\@(.*)/)
									{
										$mail=~ s/^\(//;
										$mail=~ s/\)$//;
										$mail=~ s/\.$//;
										$maillist=$mail."\;".$maillist;
									}
								}
							
								if ($maillist)
								{
									$maillist=~ s/\;$//;
									$product->{$sheet}{$count[$i]}{'SIMLOCK'}=$maillist;
									last CUST;
								}
								else 
								{
									$product->{$sheet}{$count[$i]}{'SIMLOCK'} = "";
                        	                                        push (@format_error_2,"SIMLOCK field's format error in sheet:$sheet,\tcompany:$product->{$sheet}{$count[$i]}{'COMPANY_NAME'},\tSIMLOCK:$ship");
									last CUST;
								}
							}
							else
							{
								$product->{$sheet}{$count[$i]}{'SIMLOCK'}=$ship;
								push (@format_error_3,"SIMLOCK field is empty in sheet:$sheet,\tcompany:$product->{$sheet}{$count[$i]}{'COMPANY_NAME'}");
								last CUST;
							}
						}
					}
					$next++;
				}
				$line ++;
			}
		
			$sql_stmnt="SELECT email,code_client FROM client WHERE company_id=\"$product->{$sheet}{$count[$i]}{'COMPANY_ID'}\" and country_id=\"$product->{$sheet}{$count[$i]}{'COUNTRY_ID'}\"";
        	        ($sth,$result) = Run::ex($sql_stmnt,$dbh);
                	@ref= $sth->fetchrow_array;
	                Run::finish($sth);
        	        if (@ref)
                	{
                		my ($email_db,$code_db,$country_db,$company_db)=@ref;
	                	if (($email_db eq $product->{$sheet}{$count[$i]}{'SIMLOCK'}) and ($code_db = $product->{$sheet}{$count[$i]}{'SHIPTO'}))
        	        	{
                	        }
                        	else
                        	{
	                        	my $re = "company_id:$product->{$sheet}{$count[$i]}{'COMPANY_ID'},email:$product->{$sheet}{$count[$i]}{'SIMLOCK'}\($email_db\),code_client:$product->{$sheet}{$count[$i]}{'SHIPTO'}\($code_db\),country_id:$product->{$sheet}{$count[$i]}{'COUNTRY_ID'}\($country_db\)";
        	                        push @report_key_diff,"client keys are diff in sheet:$sheet,$re";
                	        }
			}
        	        else
                	{
                        	$sql_stmnt_insert="INSERT INTO client (email,code_client,country_id,company_id) VALUE (\"$product->{$sheet}{$count[$i]}{'SIMLOCK'}\",\"$product->{$sheet}{$count[$i]}{'SHIPTO'}\",$product->{$sheet}{$count[$i]}{'COUNTRY_ID'},$product->{$sheet}{$count[$i]}{'COMPANY_ID'})";
	                        ($sth,$result) = Run::ex($sql_stmnt_insert,$dbh);
        	                if ($result ne "1")
                		{
                                	push @error, "ERROR:error to insert $sql_stmnt_insert";
		                        $sql_stmnt_del="DELETE FROM company WHERE id=$product->{$sheet}{$count[$i]}{'COMPANY_ID'}";
                	        	my ($sth_del,$result_del) = Run::ex($sql_stmnt_del,$dbh);
					Run::finish($sth_del);
				}
	                        Run::finish($sth);
        	        }
			$i++;
		}
	
	}
	else
	{
		push @error_2, "ERROR:cannot find the customers for sheet:$sheet";
	}				
}

if (@report_key_diff != "")
{
Log::print("report",@report_key_diff);
}

if (@error != "")
{
Log::print("error",@error);
}


if (@error_1 != "")
{
Log::print("error",@error_1);
}


if (@error_2 != "")
{
Log::print("error",@error_2);
}


if (@format_error != "")
{
Log::print("format",@format_error);
}

if (@format_error_1 != "")
{
Log::print("format",@format_error_1);
}

if (@format_error_2 != "")
{
Log::print("format",@format_error_2);
}

if (@format_error_3 != "")
{
Log::print("format",@format_error_3);
}

Log::print("end");
my $logfile=Log::log_file;
print "Log file saved at $logfile\n";

Log::send_mail($logfile);

exit(0);








